﻿using System;
using System.Collections.Generic;
using System.ComponentModel;
using System.Data;
using System.Drawing;
using System.Text;
using System.Windows.Forms;
using System.IO;

namespace Sbk_Manage
{
    public partial class FrmContract : Form
    {
        public FrmContract()
        {
            InitializeComponent();
        }
        ///转半角的函数(DBC case)
        ///全角空格为12288，半角空格为32
        ///其他字符半角(33-126)与全角(65281-65374)的对应关系是：均相差65248// 
        public static string ToDBC(string input)
        {
            char[] array = input.ToCharArray();
            for (int i = 0; i < array.Length; i++)
            {
                if (array[i] == 12288)
                {
                    array[i] = (char)32;
                    continue;
                }
                if (array[i] > 65280 && array[i] < 65375)
                {
                    array[i] = (char)(array[i] - 65248);
                }
            }
            return new string(array);
        }
        //定义查询字符串
        StringBuilder SqlQueryStr = new StringBuilder();
        private void BtnReset_Click(object sender, EventArgs e)
        {
            TxtHtBh.Text = "";
            TxtXmBh.Text = "";
            Txtghs.Text = "";
            TxtZbdlgs.Text = "";
            TxtHte.Text = "";
            Txtghsyhk.Text = "";
            TxtHtlrnf.Text = "";
            TxtHtmc.Text = "";
            ChkHtqdrq.Checked = false;
            ChkHtysrq.Checked = false;
            ToolStripStatusLabel1.Text = "";
        }
        //默认每页显示记录数
        int offsetRows = 120;
        //当前页数
        int CurPage = 1;
        //总记录数
        int TotalRows = 0;
        //总页数
        int TotalPages = 0;
        //最后一页的记录数
        int LastPageRows = 0;
        private void BtnQuery_Click(object sender, EventArgs e)
        {
            BtnQuery.Focus();
            //每次查询都清空
            SqlQueryStr.Remove(0, SqlQueryStr.Length);
            //组合条件
            if (!TxtHtBh.Text.Trim().Equals(""))
            {
                SqlQueryStr.AppendFormat(" and htbh='{0}'", TxtHtBh.Text.Trim().Replace("'", ""));
            }
            if (!TxtXmBh.Text.Trim().Equals(""))
            {
                SqlQueryStr.AppendFormat(" and xmbh='{0}'", TxtXmBh.Text.Trim().Replace("'", ""));
            }
            if (!Txtghs.Text.Trim().Equals(""))
            {
                SqlQueryStr.AppendFormat(" and ghs like '%{0}%'", Txtghs.Text.Trim().Replace("'", ""));
            }
            if (!TxtZbdlgs.Text.Trim().Equals(""))
            {
                SqlQueryStr.AppendFormat(" and zbdlgs like '%{0}%'", TxtZbdlgs.Text.Trim().Replace("'", ""));
            }
            if (!TxtHte.Text.Trim().Equals(""))
            {
                SqlQueryStr.AppendFormat(" and hte = {0}", TxtHte.Text.Trim().Replace("'", ""));
            }
            if (!TxtHtlrnf.Text.Trim().Equals(""))
            {
                SqlQueryStr.AppendFormat(" and  strftime('%Y', htlrrq) = '{0}'", TxtHtlrnf.Text.Trim().Replace("'", ""));
            }
            if (!Txtghsyhk.Text.Trim().Equals(""))
            {
                SqlQueryStr.AppendFormat(" and ghskpxx = '{0}'", Txtghsyhk.Text.Trim().Replace("'", ""));
            }
            if (!TxtHtmc.Text.Trim().Equals(""))
            {
                SqlQueryStr.AppendFormat(" and htmc like '%{0}%'", TxtHtmc.Text.Trim().Replace("'", ""));
            }
            if (this.ChkHtqdrq.Checked)
            {
                SqlQueryStr.AppendFormat(" and htqdrq >= '{0}'", this.dtpHtQdrq.Value.Date.ToString("s"));
            }
            if (this.ChkHtysrq.Checked)
            {
                SqlQueryStr.AppendFormat(" and htysrq >= '{0}'", this.DtpHtYsrq.Value.Date.ToString("s"));
            }
            //初次查询
            InitLoad();

        }
        string SqlPageStr = "select id,htbh,xmbh,htmc,hte,ghs,ghskpxx,zbdlgs,strftime('%Y-%m-%d',htqdrq) as htqdrq,strftime('%Y-%m-%d',htysrq) as htysrq,strftime('%Y-%m-%d %H:%M:%S',htlrrq) as htlrrq,htlrr,scan_flag,jc_flag from ht_info where del_flag=0  ";
        //初次查询
        private void InitLoad()
        {
            //分页查询，第一步是查询出总记录数；之后再分页
            string strSqlCount = "Select count(*) from ht_info where del_flag=0 " + SqlQueryStr.ToString();
            //总行数
            TotalRows = Convert.ToInt32(Program.SqliteDB.ExecuteScalar(strSqlCount));

            if (TotalRows > 0)
            {
                //总页数
                LastPageRows = TotalRows % offsetRows;
                if (LastPageRows == 0)
                {
                    TotalPages = TotalRows / offsetRows;
                }
                else
                {
                    TotalPages = TotalRows / offsetRows + 1;
                }
                //设置‘上一页’和‘下一页’按钮的可用性
                if (TotalPages > 1)
                {
                    BtnNext.Enabled = true;
                }
                else
                {
                    BtnNext.Enabled = false;
                }
                //第一次上一页不可用
                BtnPreview.Enabled = false;
                //当前页
                CurPage = 1;
                //显示限定第一页内容；形成查询字符串
                string sqlstr = SqlPageStr + SqlQueryStr.ToString() + " order by htqdrq desc,id desc LIMIT  " + offsetRows + " OFFSET  " + (CurPage - 1) * offsetRows;
                DgvHtInfo.DataSource = Program.SqliteDB.GetDataTable(sqlstr);
                if (DgvHtInfo.Rows.Count > 0)
                {
                    ToolStripStatusLabel1.Text = "共" + TotalPages + "页；当前第" + CurPage + "页；显示" + DgvHtInfo.Rows.Count.ToString() + "个合同";
                }
            }
            else
            {
                BtnPreview.Enabled = false;
                BtnNext.Enabled = false;
                //清空原有数据
                if (DgvHtInfo.Rows.Count > 0)
                {
                    for (int i = DgvHtInfo.Rows.Count - 1; i >= 0; i--)
                    {
                        DgvHtInfo.Rows.RemoveAt(i);
                    }
                }
                ToolStripStatusLabel1.Text = "不存在满足此条件的合同！请重置查询条件后查询！";
            }
        }
        //上一页
        private void BtnPreview_Click(object sender, EventArgs e)
        {
            DivPageSelect(false);
        }
        //下一页
        private void BtnNext_Click(object sender, EventArgs e)
        {
            DivPageSelect(true);
        }
        private void DivPageSelect(Boolean FX)
        {
            //判断设置按钮可用性 下一页
            if (FX == true)
            {
                CurPage += 1;
                if (CurPage == TotalPages)
                {
                    //已经到了最后一页，置'下一页'按钮不再可用
                    BtnNext.Enabled = false;
                    BtnPreview.Enabled = true;
                }
                else
                {
                    BtnPreview.Enabled = true;
                }
            }
            //上一页
            if (FX == false)
            {
                CurPage -= 1;
                if (CurPage == 1)
                {
                    //已经到了第一页，置'上一页'按钮不再可用
                    BtnPreview.Enabled = false;
                    BtnNext.Enabled = true;
                }
                else
                {
                    BtnNext.Enabled = true;
                }
            }
            //形成查询字符串
            string sqlstr = SqlPageStr + SqlQueryStr.ToString() + " order by htqdrq desc,id desc LIMIT  " + offsetRows + " OFFSET  " + (CurPage - 1) * offsetRows;
            DgvHtInfo.DataSource = Program.SqliteDB.GetDataTable(sqlstr);
            if (DgvHtInfo.Rows.Count > 0)
            {
                ToolStripStatusLabel1.Text = "共" + TotalPages + "页；当前第" + CurPage + "页；显示" + DgvHtInfo.Rows.Count.ToString() + "个合同";
            }
        }
        string OldSelectedId = "";
        //选中指定id的记录
        private void SelectedOldID(string id)
        {
            if (id.Equals(""))
            {
                return;
            }
            for (int i = 0; i < DgvHtInfo.Rows.Count; i++)
            {
                if (DgvHtInfo.Rows[i].Cells["id"].Value.ToString() == id)
                {
                    //先清空以前选中的所有行
                    DgvHtInfo.ClearSelection();
                    //最前面的黑色三角号跟着移动
                    DgvHtInfo.CurrentCell = DgvHtInfo.Rows[i].Cells["htbh"];
                    //滚动条跟着移动
                    DgvHtInfo.FirstDisplayedScrollingRowIndex = DgvHtInfo.Rows[i].Index;
                    //选中当前执行ris_no的行
                    DgvHtInfo.Rows[i].Selected = true;
                    OldSelectedId = id;
                    break;
                }
                else
                {
                    OldSelectedId = "";
                }
            }
        }
        private void FrmContract_Load(object sender, EventArgs e)
        {
            //设置datagridview的列宽自动适应,列不可排序
            for(int i  = 0;i< DgvHtInfo.ColumnCount ;i++)
            {
              DgvHtInfo.Columns[i].AutoSizeMode = DataGridViewAutoSizeColumnMode.AllCells;
            }
            TxtHte.LostFocus+=new EventHandler(TxtHte_LostFocus);
            BtnQuery.PerformClick();
        }
        private void TxtHte_LostFocus(object sender, EventArgs e)
        {
            if (!TxtHte.Text.Trim().Equals(""))
            {
                TxtHte.Text = ToDBC(TxtHte.Text);
                if (Microsoft.VisualBasic.Information.IsNumeric(TxtHte.Text.Trim()) == false)
                {
                    TxtHte.Text = "";
                    TxtHte.Focus();
                }
            }
        }

        private void AddContractBtn_Click(object sender, EventArgs e)
        {
            Frm_AddHt ins = new Frm_AddHt();
            if (ins.ShowDialog() == DialogResult.OK)
            {
                BtnReset.PerformClick();
                BtnQuery.PerformClick();
            }
        }

        private void DelContractBtn_Click(object sender, EventArgs e)
        {
             if(DgvHtInfo.RowCount > 0 && DgvHtInfo.SelectedRows.Count == 1)
             {
               //主键
               Int32 CurHtID  =Convert.ToInt32(DgvHtInfo.SelectedRows[0].Cells["id"].Value);
               //合同编号
               string CurHtbh = DgvHtInfo.SelectedRows[0].Cells["htbh"].Value.ToString();
               //合同名称
               string CurHtmc = DgvHtInfo.SelectedRows[0].Cells["htmc"].Value.ToString();
               FrmAuth ins = new FrmAuth();
               if (ins.ShowDialog() == DialogResult.OK)
               {
                   if (MessageBox.Show("确定要删除当前选定的合同{" + CurHtmc + "}麽？", "麦禾合同管理系统", MessageBoxButtons.YesNo, MessageBoxIcon.Question, MessageBoxDefaultButton.Button2) == DialogResult.Yes)
                   {
                       string sqlstr = "update ht_info set del_flag=1 where id=" + CurHtID;
                       if (Program.SqliteDB.ExecuteNonQuery(sqlstr) == 1)
                       {
                           MessageBox.Show("合同删除成功!", this.Text, MessageBoxButtons.OK, MessageBoxIcon.Information);
                           BtnQuery.PerformClick();
                       }
                   }
               }
             }
        }
        //编辑合同
        private void EditContractBtn_Click(object sender, EventArgs e)
        {
            if (DgvHtInfo.RowCount > 0 && DgvHtInfo.SelectedRows.Count == 1)
            {
                //主键
                Int32 CurHtID = Convert.ToInt32(DgvHtInfo.SelectedRows[0].Cells["id"].Value);
                //合同编号
                string CurHtbh = DgvHtInfo.SelectedRows[0].Cells["htbh"].Value.ToString();
                //合同名称
                string CurHtmc = DgvHtInfo.SelectedRows[0].Cells["htmc"].Value.ToString();
                FrmAuth ins = new FrmAuth();
                if (ins.ShowDialog() == DialogResult.OK)
                {
                    Frm_EditHt insHt = new Frm_EditHt();
                    insHt.CurHtID = CurHtID;
                    insHt.CurHtbh = CurHtbh;
                    insHt.CurHtmc = CurHtmc;
                    if (insHt.ShowDialog() == DialogResult.OK)
                    {
                        BtnQuery.PerformClick();
                    }
                }
            }
           
        }
        //合同扫描
        private void BarcodePrintBtn_Click(object sender, EventArgs e)
        {
            if (DgvHtInfo.RowCount > 0 && DgvHtInfo.SelectedRows.Count == 1)
            {
                //主键
                Int32 CurHtID = Convert.ToInt32(DgvHtInfo.SelectedRows[0].Cells["id"].Value);
                //合同编号
                string CurHtbh = DgvHtInfo.SelectedRows[0].Cells["htbh"].Value.ToString();
                //合同名称
                string CurHtmc = DgvHtInfo.SelectedRows[0].Cells["htmc"].Value.ToString();
                //合同签订日期
                string CurHtqdrq = DgvHtInfo.SelectedRows[0].Cells["htqdrq"].Value.ToString().Substring(0, 4);
                FrmAuth ins = new FrmAuth();
                if (ins.ShowDialog() == DialogResult.OK)
                {
                    Frm_Scan insHtScan = new Frm_Scan();
                    insHtScan.CurHtID = CurHtID;
                    insHtScan.CurHtbh = CurHtbh;
                    insHtScan.CurHtmc = CurHtmc;
                    insHtScan.CurHtqdrq = CurHtqdrq;
                    insHtScan.ShowDialog();
                    BtnQuery.PerformClick();
                }
            }
        }
        //双击查看扫描信息
        private void DgvHtInfo_CellMouseDoubleClick(object sender, DataGridViewCellMouseEventArgs e)
        {
            if (DgvHtInfo.RowCount > 0 && DgvHtInfo.SelectedRows.Count == 1)
            {
                //主键
                Int32 CurHtID = Convert.ToInt32(DgvHtInfo.SelectedRows[0].Cells["id"].Value);
                //合同编号
                string CurHtbh = DgvHtInfo.SelectedRows[0].Cells["htbh"].Value.ToString();
                //合同名称
                string CurHtmc = DgvHtInfo.SelectedRows[0].Cells["htmc"].Value.ToString();
                //合同签订日期
                string CurHtqdrq = DgvHtInfo.SelectedRows[0].Cells["htqdrq"].Value.ToString().Substring(0, 4);
                string CurScan_flag = DgvHtInfo.SelectedRows[0].Cells["scan_flag"].Value.ToString();
                if (CurScan_flag.Equals("1"))
                {
                    ImageProcesser.Program.CurHtID = CurHtID;
                    ImageProcesser.Program.CurHtbh = CurHtbh;
                    ImageProcesser.Program.CurHtmc = CurHtmc;
                    ImageProcesser.Program.CurHtqdrq = CurHtqdrq;
                    ImageProcesser.ImageProcessorForm ins = new ImageProcesser.ImageProcessorForm();
                    ins.ShowDialog();
                }
            }
        }
        //显示行号
        private void DgvHtInfo_RowPostPaint(object sender, DataGridViewRowPostPaintEventArgs e)
        {
           System.Drawing.Rectangle Rectangle  = new System.Drawing.Rectangle(e.RowBounds.Location.X, e.RowBounds.Location.Y, DgvHtInfo.RowHeadersWidth - 4, e.RowBounds.Height);
           TextRenderer.DrawText(e.Graphics, (e.RowIndex + 1).ToString(), DgvHtInfo.RowHeadersDefaultCellStyle.Font, Rectangle, DgvHtInfo.RowHeadersDefaultCellStyle.ForeColor, TextFormatFlags.VerticalCenter | TextFormatFlags.Right);
        }
        //格式化颜色
        private void DgvHtInfo_CellFormatting(object sender, DataGridViewCellFormattingEventArgs e)
        {
            if (DgvHtInfo.Columns[e.ColumnIndex].Name.Equals("jc_flag"))
            {
                    if (e.Value.ToString().Equals("1"))
                    {
                        e.Value = "已借出";
                        //已经借出
                        e.CellStyle.ForeColor = Color.Red;
                    }
                    else
                    {
                        e.Value = "未借出";
                    }
            }
            if (DgvHtInfo.Columns[e.ColumnIndex].Name.Equals("scan_flag"))
            {
                if (e.Value.ToString().Equals("1"))
                {
                    e.Value = "已扫描";
                    //已经扫描
                    e.CellStyle.ForeColor = Color.Green;
                }
                else
                {
                    e.Value = "未扫描";
                }
            }
        }

        private void DgvHtInfo_CellMouseClick(object sender, DataGridViewCellMouseEventArgs e)
        {
            if (e.RowIndex == -1)
            {
                return;
            }
            if (DgvHtInfo.RowCount > 0 && DgvHtInfo.SelectedRows.Count == 1)
            {
                //记录当前
                OldSelectedId = DgvHtInfo.SelectedRows[0].Cells["id"].Value.ToString();
            }
        }

        private void DgvHtInfo_DataBindingComplete(object sender, DataGridViewBindingCompleteEventArgs e)
        {
            //选中先前的那条
            if (!OldSelectedId.Equals(""))
            {
                SelectedOldID(OldSelectedId);
            }
        }
        //合同借阅
        private void JieYBtn_Click(object sender, EventArgs e)
        {
            if (DgvHtInfo.RowCount > 0 && DgvHtInfo.SelectedRows.Count == 1)
            {
                //主键
                Int32 CurHtID = Convert.ToInt32(DgvHtInfo.SelectedRows[0].Cells["id"].Value);
                //合同编号
                string CurHtbh = DgvHtInfo.SelectedRows[0].Cells["htbh"].Value.ToString();
                //合同名称
                string CurHtmc = DgvHtInfo.SelectedRows[0].Cells["htmc"].Value.ToString();
                FrmAuth ins = new FrmAuth();
                if (ins.ShowDialog() == DialogResult.OK)
                {
                    Frm_Htjy insHtJy = new Frm_Htjy();
                    insHtJy.CurHtID = CurHtID;
                    insHtJy.CurHtbh = CurHtbh;
                    insHtJy.CurHtmc = CurHtmc;
                    if (insHtJy.ShowDialog() == DialogResult.OK)
                    {
                        BtnQuery.PerformClick();
                    }
                }
            }
        }

        private void ExportExcelBtn_Click(object sender, EventArgs e)
        {
            try
            {
                DataTable dt = Program.SqliteDB.GetDataTable(SqlPageStr + " order by htqdrq desc,id asc ");
                if (dt!=null && dt.Rows.Count>0)
                {
                    //
                    SaveFileDialog sfd = new SaveFileDialog();
                    //设置文件类型 
                    sfd.Filter = "Excel文件（*.xls）|*.xls";
                    //设置默认文件类型显示顺序 
                    sfd.FilterIndex = 1;
                    //保存对话框是否记忆上次打开的目录 
                    sfd.RestoreDirectory = true;

                    //点了保存按钮进入 
                    if (sfd.ShowDialog() == DialogResult.OK)
                    {
                        string localFilePath = sfd.FileName.ToString(); //获得文件路径 
                        //存在先删除
                        if (File.Exists(localFilePath) == true)
                        {
                            File.Delete(localFilePath);
                        }
                        //创建空的
                        FileInfo fi = new FileInfo(localFilePath);
                        FileStream fs = fi.Create();
                        fs.Close();
                        fs.Dispose();
                        //
                        ExportExcelBtn.Enabled = false;
                        Aspose.Cells.Workbook wk = new Aspose.Cells.Workbook(localFilePath);
                        wk.Worksheets[0].Name ="合同信息列表";
                        AddExcelSHeet(wk,dt);
                        wk.Save(localFilePath);
                        MessageBox.Show("导出成功：" + localFilePath, "合同信息列表数据导出", MessageBoxButtons.OK, MessageBoxIcon.Information);
                        ExportExcelBtn.Enabled = true;
                    }
                }
            }
            catch (Exception ex)
            {
                MessageBox.Show("导出错误：" + ex.ToString());
            }
        }
        private void AddExcelSHeet(Aspose.Cells.Workbook wk, DataTable dt)
        {

            for (int i = 0; i < dt.Rows.Count; i++)
            {
                for (int k = 0; k < dt.Columns.Count; k++)
                {
                    wk.Worksheets[0].Cells[i, k].PutValue(dt.Rows[i][k].ToString());
                }
            }
            wk.Worksheets[0].AutoFitColumns();
            wk.Worksheets[0].AutoFitRows();
        }


    }
}
